#!/usr/bin/env python
# coding: utf-8

# In[5]:


import pandas as pd
import numpy as np
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
import seaborn as sns

try:
    df = pd.read_csv('D:/desktop/sales.csv', encoding='gbk')  # 或者尝试 'cp932'
except UnicodeDecodeError:
    df = pd.read_csv('D:/desktop/sales.csv', encoding='gbk')

# 2. 数据预处理
# 删除包含缺失值的行（销售金额、采购单价、销售单价中任一为空的记录）
df_clean = df.dropna(subset=['销售金额', '采购单价', '销售单价'])

# 检查清理后的数据
print(f"原始数据行数: {len(df)}")
print(f"清理后数据行数: {len(df_clean)}")
print("前5行数据:")
print(df_clean.head())

# 3. 准备聚类数据
X = df_clean[['销售金额', '采购单价', '销售单价']].values

# 数据标准化
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

# 4. 确定最佳聚类数量 - 肘部法则
print("\n正在计算最佳聚类数量...")
wcss = []
for i in range(1, 11):
    kmeans = KMeans(n_clusters=i, init='k-means++', random_state=42)
    kmeans.fit(X_scaled)
    wcss.append(kmeans.inertia_)
    print(f"聚类数 {i}: WCSS = {kmeans.inertia_:.2f}")

plt.figure(figsize=(10, 5))
plt.plot(range(1, 11), wcss, marker='o', linestyle='--')
plt.title('肘部法则 - 确定最佳聚类数量')
plt.xlabel('聚类数量')
plt.ylabel('WCSS (Within-Cluster Sum of Squares)')
plt.grid(True)
plt.show()

# 根据肘部法则选择聚类数量（通常选择拐点处）
n_clusters = int(input("\n根据肘部法则图，请输入选择的聚类数量: "))

# 5. 应用K-Means聚类
print(f"\n正在进行K-Means聚类，聚类数量={n_clusters}...")
kmeans = KMeans(n_clusters=n_clusters, init='k-means++', random_state=42)
clusters = kmeans.fit_predict(X_scaled)

# 将聚类结果添加到原始数据
df_clean['Cluster'] = clusters

# 6. 可视化结果

# 3D散点图
print("\n生成3D散点图...")
fig = plt.figure(figsize=(12, 8))
ax = fig.add_subplot(111, projection='3d')

scatter = ax.scatter(df_clean['销售金额'], 
                    df_clean['采购单价'], 
                    df_clean['销售单价'], 
                    c=df_clean['Cluster'], 
                    cmap='viridis',
                    s=60)

ax.set_xlabel('销售金额')
ax.set_ylabel('采购单价')
ax.set_zlabel('销售单价')
plt.title('3D商品聚类可视化')
plt.colorbar(scatter)
plt.show()

# 2D散点图矩阵
print("生成2D散点图矩阵...")
sns.pairplot(df_clean, vars=['销售金额', '采购单价', '销售单价'], 
             hue='Cluster', palette='viridis', 
             plot_kws={'alpha': 0.6, 's': 80})
plt.suptitle('商品聚类特征分布', y=1.02)
plt.show()

# 聚类特征均值雷达图
print("生成雷达图...")
cluster_means = df_clean.groupby('Cluster')[['销售金额', '采购单价', '销售单价']].mean()

# 雷达图准备
categories = cluster_means.columns
N = len(categories)
angles = [n / float(N) * 2 * np.pi for n in range(N)]
angles += angles[:1]

plt.figure(figsize=(8, 8))
ax = plt.subplot(111, polar=True)
ax.set_theta_offset(np.pi / 2)
ax.set_theta_direction(-1)
plt.xticks(angles[:-1], categories)

for i in range(len(cluster_means)):
    values = cluster_means.iloc[i].values.flatten().tolist()
    values += values[:1]
    ax.plot(angles, values, linewidth=1, linestyle='solid', 
            label=f'Cluster {i}')
    ax.fill(angles, values, alpha=0.1)

plt.title('各聚类特征均值雷达图', y=1.1)
plt.legend(loc='upper right', bbox_to_anchor=(1.3, 1.1))
plt.show()

# 7. 分析聚类结果
print("\n聚类结果统计摘要:")
cluster_summary = df_clean.groupby('Cluster').agg({
    '销售金额': ['mean', 'count'],
    '采购单价': 'mean',
    '销售单价': 'mean',
    '商品编号': lambda x: len(x.unique())
}).rename(columns={'商品编号': '唯一商品数'})

print(cluster_summary)

# 8. 保存聚类结果
output_path = '聚类结果.xlsx'
df_clean.to_excel(output_path, index=False)
print(f"\n聚类结果已保存到: {output_path}")


# In[ ]:





# In[ ]:




